﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace DataAccessObjectLayer
{
    public abstract class DBClass
    {
        /// <summary>
        /// Returns a list of objects build with the buildobject method and a given 'where'-clause.
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        public List<object> MiscWhere(string wClause)
        {
            List<object> list = new List<object>();
            String query = BuildQuery(wClause);
            var dbCon = new DBCon();
            SqlConnection connection = (SqlConnection)dbCon.GetConnection();
            DbCommand dbCommand = new SqlCommand(query, connection);
            DbDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = dbCommand;
            DataSet dataset = new DataSet("MyTable");
            dataAdapter.Fill(dataset);
            //Console.WriteLine("Dataset contains " + dataset.Tables[0].Rows.Count + " rows");
            foreach (DataRow dataRow in dataset.Tables[0].Rows)
            {
                //With miscWhere we use an unconnected state (DataSet) and therefore send a false
                //Dataset is prefered when a bigger amount of data wants to be held locally in a disconnected manner
                object o = BuildObject(dataRow, false);
                list.Add(o);
            }
            connection.Close();

            return list;
        }

        /// <summary>
        /// Returns a single object build with the buildobject method and a given 'where'-clause.
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        public object SingleWhere(string wClause)
        {
            object o = null;
            String query = BuildQuery(wClause);
            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdSw = SqlCommand(connection, out transaction);

                cmdSw.CommandText = query;
                using (cmdSw)
                {
                    DbDataReader dataReader = cmdSw.ExecuteReader();

                    if (dataReader.Read())
                    {
                        //With SingleWhere we use a connected state (DataReader) with a pointer directly into the database and therefore send a true
                        //DataReader is prefered in this case when a small amount of data is wanted and we are less interested in having a whole collection
                        o = BuildObject(dataReader, true);
                    }
                    dataReader.Close();
                    transaction.Commit();
                }
            }
            return o;
        }

        /// <summary>
        /// Builds a query with a 'where'-clause.
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        /// <returns></returns>
        protected abstract string BuildQuery(string wClause);

        /// <summary>
        /// Builds an object from either a dataset or a datareader. The bool 'connected' defines if
        /// it is one or the other.
        /// </summary>
        /// <param name="dataSetOrDataReader">A datarow or datareader.</param>
        /// <param name="connected">if set to <c>true</c> [connected](datareader).</param>
        /// <returns>Object to be cast to the correct type in the specific implementation</returns>
        protected abstract object BuildObject(object dataSetOrDataReader, bool connected);

        /// <summary>
        /// returns an SQL-command with transaction and connection set from the given ditto.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="transaction">The transaction.</param>
        protected static SqlCommand SqlCommand(SqlConnection connection, out SqlTransaction transaction)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            //Creates a command object with an association to the SqlConnection
            SqlCommand command = connection.CreateCommand();

            //Start a local transaction
            transaction = connection.BeginTransaction(IsolationLevel.Serializable);
            //transaction = connection.BeginTransaction();

            // Must assign both transaction object and connection to Command object for a pending
            // local transaction
            command.Transaction = transaction;
            command.Connection = connection;
            return command;
        }

        /// <summary>
        /// Tries to make a rollback action.
        /// </summary>
        /// <param name="e">The exception object</param>
        /// <param name="transaction">The transaction.</param>
        /// <param name="commitType">Type of the commit.</param>
        protected static void TryRollback(Exception e, SqlTransaction transaction, string commitType)
        {
            e.Data.Add("commit type that failed", commitType);
            e.Data.Add("Commit Exception Type", e.GetType());

            transaction.Rollback();
            //throw new FaultException("There was an error when trying to handle the request:\n" + e.Message);
        }
    }
}